1.31 InnoDB redo log

1. What Is redo log

• A log of the MySQL InnoDB storage engine, used for crash recovery.
• redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions.

2. Why redo log Is Needed

• According to durability requirements, once a transaction commit completes, it must be persisted to disk. There are two approaches:
  • One is to flush all pages modified by the transaction to disk before the transaction commit completes. However, this approach has very poor performance.
  • The other is logging: just record which parts of which pages were modified, and write this log to disk before commit completes. The amount of log data is very small and the I/O is sequential.

3. WAL

• Write-Ahead Logging: write the log first, then write to disk.
• 
• redo log consists of two parts: an in-memory log buffer (redo log buffer) and an on-disk log file (redo log).
• When the server starts, it requests a large contiguous memory space from the operating system called the redo log buffer.
• Every time MySQL executes a DML statement, it first appends the record to the redo log buffer.
• At certain times the log buffer is flushed to disk:
  • when log buffer space is insufficient;
  • when a transaction commits;
  • when a background thread flushes once per second;
  • when the server shuts down normally;
  • at a checkpoint.

4. Log Format

• A circular array is used underneath.
• WritePos -> CheckPoint is free space; CheckPoint -> WritePos is data that has not yet been flushed to disk.

5. redo log Write Mechanism

• 
• During transaction execution, generated redo log is first written to the redo log buffer.
• innodb_flush_log_at_trx_commit parameter:
  • when set to 0, every transaction commit only leaves the redo log in the redo log buffer;
  • when set to 1, every transaction commit directly persists the redo log to disk;
  • when set to 2, every transaction commit only writes the redo log to the page cache.
• Background thread:
  • performs write + fsync every 1 second;
  • when the space occupied by the redo log buffer is about to reach half of innodb_log_buffer_size, the background thread proactively writes to disk.
• Group commit:
  • assign an LSN to each redo-log; redo-logs from multiple concurrent transactions are fsynced together, and the bin-log can also be fsynced together.

6. redo-log vs bin-log

	redo-log	bin-log
Layer	Storage-engine layer, specific to InnoDB	Server layer, usable by all engines
Log format	Physical log; records “what modification was made on a certain data page.” See Distributed System Replication.md	Logical log; records the original logic of the statement, for example “increment column c by 1 for the row with ID=2.” See Distributed System Replication.md
Write method	Circular writing. The space is fixed and will be reused when full	Append writing. When a binlog file reaches a certain size, it switches to the next file and does not overwrite previous logs

7. How to Keep redo-log and bin-log Consistent

7.1. Two-Phase Commit

1. Write the redo-log; it is in the prepare state.
2. Write the bin-log.
3. Commit the transaction; the redo-log enters the commit state.

The following explanations assume the data is in memory:

If MySQL crashes at time A, after restart and crash recovery it finds that the redo-log is in the prepare state and the bin-log has not been written, so it rolls back directly.

If MySQL crashes at time B, after restart and crash recovery it finds that the redo-log is in the prepare state and the bin-log has already been written. The integrity of the bin-log needs to be checked; if it is complete, the transaction is committed directly.

If MySQL crashes at time C, after restart and crash recovery it finds that the redo-log is in the commit state, which means the redo-log and bin-log are complete, so it commits directly.

8. References

• MySQL :: MySQL 8.0 Reference Manual :: 15.6.5 Redo Log
• The Three Major MySQL Logs You Must Understand - binlog, redo log and undo log - SegmentFault
• Log System: How Is an SQL UPDATE Statement Executed?
• Q&A Article (1): Log and Index Related Questions